package ncs.admin.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ncs.common.database.UserDAO;
import ncs.common.model.Course;
import ncs.common.model.exception.TeacherNotExistException;
import ncs.common.util.ConnectDB;

/**
 * @author Jian
tb_course
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| course_id   | int(11)      | NO   | PRI | NULL              | auto_increment |
| course_name | varchar(256) | YES  |     | NULL              |                |
| admin_id    | int(11)      | YES  |     | NULL              |                |
| create_time | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+--------------+------+-----+-------------------+----------------+
view_course
+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| ID          | int(11)      | NO   |     | 0                   |       |
| name        | varchar(256) | YES  |     | NULL                |       |
| adminID     | varchar(20)  | YES  |     | NULL                |       |
| adminName   | varchar(50)  | YES  |     | NULL                |       |
| teacherID   | varchar(20)  | YES  |     | NULL                |       |
| teacherName | varchar(50)  | YES  |     | NULL                |       |
| createTime  | timestamp    | NO   |     | 0000-00-00 00:00:00 |       |
+-------------+--------------+------+-----+---------------------+-------+
 */
public class CourseDAO implements ICourseDAO{

	@Override
	public void addCourse(Course course) throws Exception {
		if(false==UserDAO.isUserExist(course.getTeacher_id()))
			throw new TeacherNotExistException("");
		Connection conn=ConnectDB.getConnection();
		conn.setAutoCommit(false);
		String sql="insert into tb_course values(?,?,?,?)";
		PreparedStatement ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		ps.setInt(1, 0);
		ps.setString(2, course.getCourse_name());
		ps.setInt(3, course.getAdmin_db_id());
		ps.setDate(4, null);
		ps.execute();
		ResultSet rs=ps.getGeneratedKeys();
		rs.next();
		course.setCourse_id(rs.getInt(1));
		rs.close();
		ps.close();
		
		sql="insert into tb_teacher_course values(?,?)";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, UserDAO.getUserDbId(course.getTeacher_id()));
		ps.setInt(2, course.getCourse_id());
		try
		{
			ps.execute();
		}catch(Exception e)
		{
			conn.rollback();
			ps.close();
			conn.close();
			throw e;
		}
		conn.commit();
		ps.close();
		conn.close();
	}
	public List<Course>getCourses(int start, int limits)throws Exception
	{
		String sql="select * from view_course limit ?,?";
		Connection conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, start);
		ps.setInt(2, limits);
		ResultSet rs=ps.executeQuery();
		List<Course>course_list=new ArrayList<Course>();
		while(rs.next())
		{
			Course course=new Course();
			course.setCourse_id(rs.getInt(1));
			course.setCourse_name(rs.getString(2));
			course.setAdmin_id(rs.getString(3));
			course.setAdmin_name(rs.getString(4));
			course.setTeacher_id(rs.getString(5));
			course.setTeacher_name(rs.getString(6));
			course.setCreate_time(rs.getString(7));
			course.setStudent_count(0);
			course_list.add(course);
		}
		rs.close();
		ps.close();
		conn.close();
		return course_list;
	}
	public  int getCourseCount() throws Exception
	{
		String sql="select count(*) from tb_course";
		Connection conn=ConnectDB.getConnection();
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery(sql);
		rs.next();
		int ret=rs.getInt(1);
		rs.close();
		st.close();
		conn.close();
		return ret;
	}
}
